/*
 * dbHelper.java
 *
 * Created on December 5, 2009, 9:07 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package DatabaseAccess;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.JdbcRowSet;
import com.sun.rowset.CachedRowSetImpl;
import com.sun.rowset.JdbcRowSetImpl;
import java.sql.CallableStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Vector;
import java.util.Date;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.DatabaseMetaData;
import java.sql.CallableStatement;
import java.sql.ResultSetMetaData;
import java.sql.PreparedStatement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Dao Trong Duy
 */
public class D_DBHelper {

    /** Creates a new instance of dbHelper */
    private Connection conn = null;
    private CachedRowSet crs=null;
    private JdbcRowSet jrs=null;
    private PreparedStatement pre = null;
    private Statement st= null;
    private ResultSet rs = null;
    private DatabaseMetaData dbm=null;
    private ResultSetMetaData rsMetaData=null;
    private CallableStatement cst=null;
    private String url="";
    private String serverName="";
    private String portNumber="";
    private String databaseName="";
    private String userName="";
    private String passWord="";
    public static final int MODE_PREPARED 	= 0;
    public static final int MODE_CALLABLE 	= 1;
    public static final int MODE_NORMAL 	= 2;
    private int mode = MODE_NORMAL;

    public D_DBHelper() {
    }
    //setup connection
     public Connection open(String url, String username, String pass) throws Exception {
        try{
            conn = DriverManager.getConnection(url,username, pass);
        }catch(Exception e){
            return null;
        }
        return conn;
    }

    public Connection open(String url) throws Exception {
        conn = DriverManager.getConnection(url,"sa", "sa");
        return conn;
    }

    public boolean setupConnection(){
        try {
            //load the driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException ex) {
            return false;
        }
        try {
            //open the connection
            //create connection string
            conn = DriverManager.getConnection(getConnectionUrl(),userName,passWord);
        } catch (SQLException ex) {
            return false;

        }
        return true;
    }

    public boolean setupConnection(String dbName,String uid, String pwd,int port){
        try {
            //load the driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException ex) {
            return false;
        }
        try {
            //open the connection
            //create connection string
            String url = "jdbc:sqlserver://localhost:"+port+";database="+dbName+";user="+uid+";password="+pwd;
            conn = DriverManager.getConnection(url);
        } catch (SQLException ex) {
            return false;
        }
        return true;
    }

   public String getConnectionUrl(){
        url="jdbc:sqlserver://"+ serverName+":" + portNumber+ ";databaseName=" + databaseName;
        return url;
   }

   public void setConnectionUrl(String fileNameConnection) throws SQLException
   {
        Properties p = new Properties();

        try {
          FileInputStream fis = new FileInputStream(fileNameConnection);
          p.load(fis);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(D_DBHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch(IOException e)
        {
            e.printStackTrace();
        }
        serverName= p.getProperty("ServerName");
        portNumber= p.getProperty("Port");
        databaseName =p.getProperty("DatabaseName");
        userName = p.getProperty("Username");
        passWord = p.getProperty("Password");
        url="jdbc:sqlserver://" + serverName + ":" + portNumber + ";databaseName=" + databaseName;
    }

   public void setServerName(String serverName){
      this.serverName=serverName;
   }
   public String getServerName(){
    return this.serverName;
   }

   public void setDatabaseName(String databaseName){
    this.databaseName=databaseName;
   }
   public String  getDatabaseName(){
      return this.databaseName;
   }

   public void setConnectionPort(String protNumber){
    this.portNumber=portNumber;
   }
   public String  getConnectionPort(){
      return this.portNumber;
   }

   public void setUserName(String userName){
    this.userName=userName;
   }
   public String  getUserName(){
      return this.userName;
   }

   public void setPassWord(String passWord){
    this.passWord=passWord;
   }
   public String  getPassWord(){
      return this.passWord;
   }

    //close the connection
    protected void finalize() throws Throwable {
       if(crs!=null){
            crs.close();
            crs=null;
        }
        if(cst!=null){
            cst.close();
            cst=null;
        }
        if(jrs!=null){
            jrs.close();
            jrs=null;
        }
        if (rs != null) {
            rs.close();
            rs=null;
        }
        if (pre != null) {
            pre.close();
            pre=null;
        }
        if (conn != null) {
            conn.close();
            conn = null;
        }
    }

    public void close() throws Exception {
        if(crs!=null){
            crs.close();
            crs=null;
        }
        if(cst!=null){
            cst.close();
            cst=null;
        }
        if(jrs!=null){
            jrs.close();
            jrs=null;
        }
        if (rs != null) {
            rs.close();
            rs=null;
        }
        if (pre != null) {
            pre.close();
            pre=null;
        }
        if (conn != null) {
            conn.close();
            conn = null;
        }
    }

                            //***** USING RESULTSET ****
    public ResultSet executeProcedure(String spName)throws SQLException{
        return conn.prepareCall("{call "+spName+"()}").executeQuery();
    }

    public ResultSet executeProcedure(String spName, int ID)throws SQLException{
        String callSP = "{call "+spName+" (";
        callSP += ID;
        callSP += ")}";
        return conn.prepareCall(callSP).executeQuery();
    }

    //execute a store procedure, with some arguments
    //params is a list of parameters
    //delete, insert, update
    public ResultSet executeProcedure(String spName,Vector params)throws SQLException{
        String callSP = "{call "+spName+" (";
        //visit all elements of params
        for(int i = 0; i < params.size(); i++){
            //if params[i] is a number
            Object v = params.get(i);
            if(v instanceof Integer)
                callSP += Integer.parseInt(v.toString());
            else if(v instanceof Float)
                callSP += Float.parseFloat(v.toString());
            else if(v instanceof Date){
                callSP +=String.valueOf(v);
            }
            else //v is a string , a datetime
                callSP += "'"+v.toString()+"'";

            if( i < params.size() - 1)
                callSP += ",";
        }
        callSP += ")}";
        return conn.prepareCall(callSP).executeQuery();
    }

    public boolean executeProcedureUpdate(String spName,Vector params)throws SQLException{
        //System.out.println("");
        String callSP = "{call "+spName+" (";
        //visit all elements of params
        for(int i = 0; i < params.size(); i++){
            //if params[i] is a number
            Object v = params.get(i);
            if(v instanceof Integer)
                callSP += Integer.parseInt(v.toString());
            else if(v instanceof Float)
                callSP += Float.parseFloat(v.toString());
            else if(v instanceof Date){
                callSP +=String.valueOf(v);
            }
            else //v is a string , a datetime
                callSP += "'"+v.toString()+"'";
            if( i < params.size() - 1)
                callSP += ",";
        }
        callSP += ")}";
        return conn.prepareCall(callSP).execute();
    }


    public boolean executeProcedureUpdate(String spName)throws SQLException{
        //System.out.println("");
        return conn.prepareCall("{call "+spName+"()}").execute();
    }


     public ResultSet doQuery(String sql) throws SQLException {
        rs = null;
        if (mode == MODE_NORMAL) {
            st = conn.createStatement();
            rs = st.executeQuery(sql);
        }
        return rs;
    }


    public ResultSet doQuery() throws SQLException {
        rs = null;
        if (pre != null) {
            rs = pre.executeQuery();
            pre = null;
        }
        return rs;
    }
     public int doUpdate(String sql) throws SQLException {
        if (mode == MODE_NORMAL) {
            st = conn.createStatement();
            return st.executeUpdate(sql);
        } else {
            return 0;
        }
    }

    public int doUpdate() throws SQLException {
        int rows = 0;
        if (pre != null) {
            rows = pre.executeUpdate();
        }
        return rows;
    }

    public ResultSet doExecuteRS() throws SQLException {
        boolean bool = false;
        if (pre != null) {
            return pre.executeQuery();
        }
        return null;
    }

               //***** USING CACHEDROWSET *****
   public CachedRowSet executeProcedureQueryCachedRowSet(String spName)throws SQLException{
        cst=conn.prepareCall("{call "+spName+"()}",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        rs=cst.executeQuery();
        crs=new CachedRowSetImpl();
        crs.setUsername(userName);
        crs.setPassword(passWord);
        crs.setUrl(getConnectionUrl());
        crs.populate(rs,1);
        return crs;
    }
    public CachedRowSet executeProcedureQueryCachedRowSet(String spName,Vector params)throws SQLException{
        String callSP = "{call "+spName+" (";
        //visit all elements of params
        for(int i = 0; i < params.size(); i++){
            //if params[i] is a number
            Object v = params.get(i);
            if(v instanceof Integer)
                callSP += Integer.parseInt(v.toString());
            else if(v instanceof Float)
                callSP += Float.parseFloat(v.toString());
            else if(v instanceof Date){
                callSP +=String.valueOf(v);
            }
            else //v is a string , a datetime
                callSP += "'"+v.toString()+"'";
            if( i < params.size() - 1)
                callSP += ",";
        }
        callSP += ")}";
        cst=conn.prepareCall(callSP,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        rs=cst.executeQuery();
        crs=new CachedRowSetImpl();
        crs.setUsername(userName);
        crs.setPassword(passWord);
        crs.setUrl("jdbc:sqlserver://"+serverName+";databaseName="+databaseName);
        crs.populate(rs,1);
        return crs;
    }

    //***** USING JDBCROWSET *****
        public JdbcRowSet executeProcedureQueryJdbcRowSet(String spName)throws SQLException{
        cst=conn.prepareCall("{call "+spName+"()}",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        rs=cst.executeQuery();
        jrs=new JdbcRowSetImpl(rs);
        return jrs;
    }


     public JdbcRowSet executeProcedureQueryJdbcRowSet(String spName,Vector params)throws SQLException{
        String callSP = "{call "+spName+" (";
        //visit all elements of params
        for(int i = 0; i < params.size(); i++){
            //if params[i] is a number
            Object v = params.get(i);
            if(v instanceof Integer)
                callSP += Integer.parseInt(v.toString());
            else if(v instanceof Float)
                callSP += Float.parseFloat(v.toString());
            else if(v instanceof Date){
                callSP +=String.valueOf(v);
            }
            else //v is a string , a datetime
                callSP += "'"+v.toString()+"'";
            if( i < params.size() - 1)
                callSP += ",";
        }
        callSP += ")}";
        cst=conn.prepareCall(callSP ,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        rs=cst.executeQuery();
        jrs=new JdbcRowSetImpl(rs);
        return jrs;
    }
    //****************



    public String[] getTableNames() throws SQLException {
        int size = 0,i = 0;
        dbm = conn.getMetaData();
        String[] types = {"TABLE"};
        rs = dbm.getTables(null, null, "%", types);
        size = rs.getMetaData().getColumnCount();
        String[] tables = new String[size];

        while (rs.next()) {
            tables[i] = rs.getString("TABLE_NAME");
            i++;
        }
        return tables;
    }

      public String[] getColumnNames(String table) throws SQLException {
        st= conn.createStatement();
        rs = st.executeQuery("Select * From " + table);
        rsMetaData = rs.getMetaData();
        String[] columns = new String[rsMetaData.getColumnCount()];
        for (int i = 0; i < columns.length; i++) {
            columns[i] = rs.getString(i + 1);
        }
        return columns;
    }
    //execute a store procedure, no arguments
    //show all values

    public void setDouble(int idx, double value) throws SQLException {
        pre.setDouble(idx, value);
    }

    public void setInt(int idx, int value) throws SQLException {
        pre.setInt(idx, value);
    }

    public int getInt(int idx) throws SQLException {
        return ((CallableStatement) pre).getInt(idx);
    }

    public void setString(int idx, String value) throws SQLException {
        pre.setString(idx, value);
    }

    public String getString(int idx) throws SQLException {
        return ((CallableStatement) pre).getString(idx);
    }

    public void setBoolean(int idx, boolean value) throws SQLException {
        pre.setBoolean(idx, value);
    }

    public boolean getBoolean(int idx) throws SQLException {
        return ((CallableStatement) pre).getBoolean(idx);
    }

    public void setStatement(String sql) throws SQLException {
        if (conn != null) {
            if (mode == MODE_PREPARED) {
                pre = conn.prepareStatement(sql);
            }
            if (mode == MODE_CALLABLE) {
                st = conn.prepareCall(sql);
            }
        }
    }

    public boolean doExecute() throws SQLException {
        boolean bool = false;
        if (pre != null) {
            bool = pre.execute();
        }
        return bool;
    }

    public void setMode(int mod) {
        this.mode = mod;
    }

}
